package jdbcConnect;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileOutputStream;
import java.sql.*;
import java.util.Scanner;

public class User {
    String loginuser;
    String loginpwd;
    Connection conn = new JDBC().JDBCConnectvoid();
    Statement stmt = conn.createStatement();
    ResultSet rs = null;
    public User(String user,String pwd) throws SQLException {
        this.loginuser=user;
        this.loginpwd=pwd;
    }

    public User() throws SQLException {
    }

    public void Update() throws SQLException {
        Scanner s=new Scanner(System.in);
        System.out.println("请输入需要修改的学生学号：");
        String UpStuNo = s.nextLine();
        String sql = "select * from student where StudentNo='" + UpStuNo + "'";
        rs=stmt.executeQuery(sql);
        while(rs.next()){
            System.out.println(rs.getString(1) + "->" + rs.getString(2) + "->" + rs.getString(3) + "->" + rs.getString(4) + "->" + rs.getString(5));
        }
        System.out.println("请对该学生信息进行修改：");
        System.out.println("请依次输入该学生的姓名、年龄、性别、密码！");
        String TUpStuName = s.next();
        String TUpStuAge = s.next();
        String TUpStuSex = s.next();
        String TUpStuPwd = s.next();

        String UpStsql = "update student set StudentName='" + TUpStuName + "',age='" + TUpStuAge + "',sex='" + TUpStuSex + "',password='" + TUpStuPwd + "' where StudentNo='" + UpStuNo + "';";
        int Num = stmt.executeUpdate(UpStsql);
        if (Num != 0) System.out.println("修改成功！");
        else System.out.println("修改失败！");
    }
    public void Select() throws SQLException {
        String sql = "select * from student";
        JDBC jdbc = new JDBC();
        Connection conn = jdbc.JDBCConnectvoid();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        System.out.println("以下是打印的学生表信息");
        while (rs.next()) {
            System.out.println(rs.getString(1) + "->" + rs.getString(2) + "->" + rs.getString(3) + "->" + rs.getString(4) + "->" + rs.getString(5));
        }
    }
    public  void OutExcel() throws Exception {
        String path = "D:/IDEA/project/JDBCProject/全体学生数据.xls";
        //创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建表对象
        HSSFSheet sheet = workbook.createSheet("student");

            //调用JDBC无参构造方法
            //创建JDBC对象
            JDBC jdbc = new JDBC();
            Class.forName(jdbc.driver);
            // 2、获取连接，返回连接对象connection
            conn = jdbc.JDBCConnectvoid();
            // 3、获取数据库操作对象
            stmt = conn.createStatement();
            // 2、获取连接
            String sql = "select * from student";
            rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();//得到结果集的字段名
            int c = rsmd.getColumnCount();//得到数据表的结果集的字段数量
            //生成表单的第一行（表头）
            //创建行对象
            HSSFRow row = sheet.createRow(0);//创建第一行
            //写入表头数据
            for (int i = 0; i < c; i++) {
                HSSFCell cell = row.createCell(i);//创建第一行的第i列
                //写入数据
                cell.setCellValue(rsmd.getColumnName(i + 1));
            }
            int r = 1;
            while (rs.next()) {
                HSSFRow row1 = sheet.createRow(r++);
                for (int i = 0; i < c; i++) {
                    HSSFCell cel = row1.createCell(i);
                    cel.setCellValue(rs.getString(i + 1));
                }
            }
            //用文件输出流创建Excel表
            FileOutputStream outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
            System.out.println("导出成功！");
            outputStream.close();
    }

}

